use std::collections::HashMap;

use chrono::{Local, Utc};
use sea_orm::{
    sea_query::{
        self, Alias, DeleteStatement, Expr, Index, IndexCreateStatement, InsertStatement,
        SchemaStatementBuilder, SelectStatement, SimpleExpr, UpdateStatement,
    },
    DatabaseBackend, QueryResult, Statement,
};
use sea_query::{Cond, Condition, LikeExpr};
use serde::{Deserialize, Deserializer};

use crate::{JsonMap, JsonValue};

static ORDER_MATCH: once_cell::sync::Lazy<regex::Regex> = once_cell::sync::Lazy::new(|| {
    regex::Regex::new(r"^\w+(-desc|-asc)?(,\w+(-desc|-asc)?)*$").unwrap()
});
static RANDSTR_MATCH: once_cell::sync::Lazy<regex::Regex> =
    once_cell::sync::Lazy::new(|| regex::Regex::new(r"^RandStr(?:\((\d+)\))?$").unwrap());
static FILTER_MATCH: once_cell::sync::Lazy<regex::Regex> =
    once_cell::sync::Lazy::new(|| regex::Regex::new(r"^(\w+)-(like|eq)-(\w+)$").unwrap());
static STRING_MATCH: once_cell::sync::Lazy<regex::Regex> =
    once_cell::sync::Lazy::new(|| regex::Regex::new(r"^String(?:\((\d+)\))?$").unwrap());
static CHAR_MATCH: once_cell::sync::Lazy<regex::Regex> =
    once_cell::sync::Lazy::new(|| regex::Regex::new(r"^Char(?:\((\d+)\))?$").unwrap());

#[derive(Debug, Default)]
pub struct Orders(Vec<(String, bool)>);

impl std::ops::Deref for Orders {
    type Target = Vec<(String, bool)>;

    fn deref(&self) -> &Self::Target {
        &self.0
    }
}

impl From<&str> for Orders {
    fn from(value: &str) -> Self {
        Self(if ORDER_MATCH.is_match(value) {
            value
                .split(",")
                .filter(|s| !s.is_empty())
                .map(|f| f.split("-"))
                .map(|mut f| {
                    (
                        f.next().unwrap().to_owned(),
                        f.next().map(|f| f == "asc").unwrap_or(true),
                    )
                })
                .collect()
        } else {
            vec![]
        })
    }
}

impl Orders {
    pub fn merge(mut self, orders: &Self) -> Self {
        self.0.extend(orders.0.clone());
        self
    }
}

impl<'de> Deserialize<'de> for Orders {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: Deserializer<'de>,
    {
        String::deserialize(deserializer).map(|f| f.as_str().into())
    }
}

#[derive(Debug, Clone, Deserialize)]
pub enum Filter {
    Eq(String, String),
    Like(String, String),
    NotLike(String, String),
    And(Vec<Filter>),
    Or(Vec<Filter>),
}

impl Filter {
    pub fn get_cond(&self) -> Condition {
        match &self {
            Self::Eq(name, value) => Cond::any().add(Expr::col(Alias::new(&name)).eq(value)),
            Self::Like(name, value) => {
                Cond::any().add(Expr::col(Alias::new(&name)).like(LikeExpr::str(value)))
            }
            Self::NotLike(name, value) => {
                Cond::any().add(Expr::col(Alias::new(&name)).not_like(LikeExpr::str(value)))
            }
            Self::And(filters) => filters
                .iter()
                .fold(Cond::all(), |expr, x| expr.add(x.get_cond())),
            Self::Or(filters) => filters
                .iter()
                .fold(Cond::any(), |expr, x| expr.add(x.get_cond())),
        }
    }

    #[deprecated]
    pub fn get_expr(&self) -> SimpleExpr {
        match &self {
            Self::Eq(name, value) => Expr::col(Alias::new(&name)).eq(value),
            Self::Like(name, value) => Expr::col(Alias::new(&name)).like(LikeExpr::str(value)),
            Self::NotLike(name, value) => {
                Expr::col(Alias::new(&name)).not_like(LikeExpr::str(value))
            }
            Self::And(filters) => filters
                .iter()
                .fold(Expr::value(1), |expr, x| expr.and(x.get_expr())),
            Self::Or(filters) => filters
                .iter()
                .fold(Expr::value(0), |expr, x| expr.or(x.get_expr())),
        }
    }
}

impl Into<Vec<Filter>> for Filter {
    fn into(self) -> Vec<Filter> {
        vec![self]
    }
}

impl Into<Filter> for (String, String) {
    fn into(self) -> Filter {
        Filter::Eq(self.0, self.1)
    }
}

impl TryFrom<&str> for Filter {
    type Error = String;

    fn try_from(value: &str) -> Result<Self, Self::Error> {
        let caps = FILTER_MATCH.captures(value);
        if let Some(caps) = caps {
            let opt = caps.get(2).unwrap().as_str();
            Ok(match opt {
                "like" => Self::Like(
                    caps.get(1).unwrap().as_str().to_owned(),
                    caps.get(3).unwrap().as_str().to_owned(),
                ),
                "eq" => Self::Eq(
                    caps.get(1).unwrap().as_str().to_owned(),
                    caps.get(3).unwrap().as_str().to_owned(),
                ),
                _ => Err("()".to_string())?,
            })
        } else {
            Err("()".to_string())
        }
    }
}

#[derive(Debug, Clone, Deserialize)]
pub struct GenIndex {
    name: Option<String>,
    columns: Vec<String>,
    primary: Option<bool>,
    unique: Option<bool>,
}

impl GenIndex {
    fn index(&self) -> IndexCreateStatement {
        let mut index = Index::create();
        if let Some(name) = &self.name {
            index.name(name);
        }
        if Some(true) == self.primary {
            index.primary();
        }
        if Some(true) == self.unique {
            index.unique();
        }
        for col in &self.columns {
            index.col(Alias::new(&col));
        }
        return index;
    }
}

#[derive(Debug, Deserialize, Clone)]
#[allow(dead_code, unused)]
pub struct GenTableSerde {
    table_name: String,
    table_comment: Option<String>,
    columns: Vec<GenColumn>,
    #[serde(default)]
    indexs: Vec<GenIndex>,
}

impl From<GenTableSerde> for GenTable {
    fn from(value: GenTableSerde) -> Self {
        let mut columns: HashMap<String, GenColumn> = HashMap::new();
        let mut columns_vec: Vec<String> = vec![];
        for col in value.columns {
            let key = col.name().to_owned();
            columns.insert(key.clone(), col);
            columns_vec.push(key);
        }
        Self {
            table_name: value.table_name,
            table_comment: value.table_comment,
            columns,
            indexs: value.indexs,
            columns_vec: Some(columns_vec),
        }
    }
}

#[derive(Debug, Deserialize, Clone)]
#[serde(from = "GenTableSerde")]
#[allow(dead_code, unused)]
pub struct GenTable {
    table_name: String,
    table_comment: Option<String>,
    columns_vec: Option<Vec<String>>,
    columns: HashMap<String, GenColumn>,
    // #[serde(default)]
    indexs: Vec<GenIndex>,
}

impl GenTable {
    pub fn alias(&self) -> Alias {
        Alias::new(&self.table_name)
    }

    pub fn get_pk_filter<S: Into<String>>(&self, value: S) -> Filter {
        Filter::Eq("()".to_owned(), value.into())
    }

    pub fn get_column_by_name(&self, name: &str) -> Option<&GenColumn> {
        self.columns.get(name)
    }

    pub fn coloumns_map(&self, columns: Option<Vec<String>>) -> HashMap<String, &GenColumn> {
        let mut cols: HashMap<String, &GenColumn> = HashMap::new();
        if let Some(columns) = columns {
            for col in columns {
                let v = self.columns.get(&col).unwrap();
                cols.insert(col, v);
            }
        } else {
            self.columns.iter().for_each(|f| {
                cols.insert(f.0.to_owned(), f.1);
            })
        }
        cols
    }

    pub fn table_create_statement(&self) -> sea_query::TableCreateStatement {
        let mut table_sql = sea_query::Table::create();
        table_sql.table(self.alias()).if_not_exists();
        if let Some(columns) = &self.columns_vec {
            for col in columns {
                table_sql.col(&mut self.columns.get(col).unwrap().column_def());
            }
        } else {
            self.columns
                .iter()
                .map(|f| f.1.column_def())
                .for_each(|mut f| {
                    table_sql.col(&mut f);
                });
        }
        for index in &self.indexs {
            table_sql.index(&mut index.index());
        }
        table_sql
    }

    pub fn update_statement(&self) -> UpdateStatement {
        let mut sql = sea_query::Query::update();
        sql.table(self.alias());
        return sql;
    }

    pub fn delete_statement(&self) -> DeleteStatement {
        let mut sql = sea_query::Query::delete();
        sql.from_table(self.alias());
        return sql;
    }

    pub fn map_result(&self, result: &QueryResult) -> JsonMap {
        let mut map = serde_json::Map::new();
        self.columns.iter().for_each(|f| {
            map.insert(f.0.clone(), f.1.try_get_value_from(result, "").unwrap());
        });
        map
    }
}

#[derive(Debug, Clone, Deserialize)]
pub enum ColumnType {
    Char,
    String,
    Text,
    Boolean,
    TinyInteger,
    SmallInteger,
    Integer,
    BigInteger,
    TinyUnsigned,
    SmallUnsigned,
    Unsigned,
    BigUnsigned,
    Float,
    Double,
    DateTime,
    Timestamp,
    Time,
    Date,
    Json,
}

impl ColumnType {
    pub fn map_query_value(
        &self,
        query_result: &QueryResult,
        pre: &str,
        col: &str,
    ) -> Result<JsonValue, sea_orm::DbErr> {
        match self {
            ColumnType::TinyInteger => query_result.try_get::<u8>(pre, col).map(From::from),
            ColumnType::TinyUnsigned => query_result.try_get::<i8>(pre, col).map(From::from),
            ColumnType::SmallInteger => query_result.try_get::<i16>(pre, col).map(From::from),
            ColumnType::SmallUnsigned => query_result.try_get::<u16>(pre, col).map(From::from),
            ColumnType::Integer => query_result.try_get::<i32>(pre, col).map(From::from),
            ColumnType::Unsigned => query_result.try_get::<u32>(pre, col).map(From::from),
            ColumnType::BigInteger => query_result.try_get::<i64>(pre, col).map(From::from),
            ColumnType::BigUnsigned => query_result.try_get::<u64>(pre, col).map(From::from),
            ColumnType::Json => query_result.try_get::<JsonValue>(pre, col),
            ColumnType::Boolean => query_result.try_get::<bool>(pre, col).map(From::from),
            ColumnType::Double | ColumnType::Float => {
                query_result.try_get::<f64>(pre, col).map(From::from)
            }
            ColumnType::Char | ColumnType::Text | ColumnType::String => {
                query_result.try_get::<String>(pre, col).map(From::from)
            }
            ColumnType::DateTime => query_result
                .try_get::<chrono::NaiveDateTime>(pre, col)
                .map(|d| d.to_string())
                .map(From::from),
            ColumnType::Timestamp => query_result
                .try_get::<chrono::DateTime<Utc>>(pre, col)
                .map(|d| d.to_string())
                .map(From::from),
            ColumnType::Time => todo!(),
            ColumnType::Date => todo!(),
        }
    }
}

macro_rules! ss {
    ($re:ident,$value:expr) => {
        if $value == stringify!($re) {
            return Ok(Self::$re);
        }
    };
}

impl TryFrom<String> for ColumnType {
    type Error = String;

    fn try_from(value: String) -> Result<Self, Self::Error> {
        ss!(DateTime, value);
        ss!(String, value);
        ss!(Text, value);
        ss!(BigInteger, value);
        ss!(Unsigned, value);
        ss!(BigUnsigned, value);
        ss!(Timestamp, value);
        ss!(Integer, value);
        ss!(Boolean, value);
        ss!(DateTime, value);
        Err(format!("unknown variant `{}`", value))
    }
}

#[derive(Debug, Clone)]
pub enum DefaultValueFunc {
    DateTime,
    Date,
    Timestamp,
    RandStr(usize),
    UuidV4,
    True,
    False,
}

impl<'de> Deserialize<'de> for DefaultValueFunc {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: Deserializer<'de>,
    {
        String::deserialize(deserializer)?
            .try_into()
            .map_err(serde::de::Error::custom)
    }
}

impl TryFrom<String> for DefaultValueFunc {
    type Error = String;

    fn try_from(value: String) -> Result<Self, Self::Error> {
        ss!(DateTime, value);
        ss!(Date, value);
        ss!(Timestamp, value);
        ss!(UuidV4, value);
        ss!(True, value);
        ss!(False, value);
        if let Some(cap) = RANDSTR_MATCH.captures(&value) {
            return Ok(Self::RandStr(
                cap.get(1)
                    .map(|f| f.as_str().parse::<usize>())
                    .map(|f| f.unwrap_or(12))
                    .unwrap_or(8),
            ));
        }
        Err(format!("unknown variant `{}`", value))
    }
}

impl Into<SimpleExpr> for &DefaultValueFunc {
    fn into(self) -> SimpleExpr {
        match self {
            DefaultValueFunc::DateTime => crate::util::current_datetime().into(),
            DefaultValueFunc::Date => crate::util::current_date().into(),
            DefaultValueFunc::Timestamp => crate::util::current_timestamp().into(),
            DefaultValueFunc::RandStr(size) => crate::util::rand_str(*size).into(),
            DefaultValueFunc::UuidV4 => crate::util::uuid_v4_str().into(),
            DefaultValueFunc::True => true.into(),
            DefaultValueFunc::False => false.into(),
        }
    }
}

impl Into<JsonValue> for &DefaultValueFunc {
    fn into(self) -> JsonValue {
        match self {
            DefaultValueFunc::DateTime => crate::util::current_datetime().to_string().into(),
            DefaultValueFunc::Date => crate::util::current_date().to_string().into(),
            DefaultValueFunc::Timestamp => crate::util::current_timestamp().timestamp().into(),
            DefaultValueFunc::RandStr(size) => crate::util::rand_str(*size).into(),
            DefaultValueFunc::UuidV4 => crate::util::uuid_v4_str().into(),
            DefaultValueFunc::True => JsonValue::Bool(true),
            DefaultValueFunc::False => JsonValue::Bool(false),
        }
    }
}

fn columntype_deserialize<'de, D>(deserializer: D) -> Result<ColumnType, D::Error>
where
    D: Deserializer<'de>,
{
    let s = String::deserialize(deserializer)?;
    ColumnType::try_from(s).map_err(|e| serde::de::Error::custom(e))
}

#[derive(Debug, Clone, Deserialize)]
#[allow(dead_code)]
pub struct GenColumn {
    name: Option<String>,
    column_name: String,
    #[serde(deserialize_with = "columntype_deserialize")]
    column_type: ColumnType,
    column_type_len: Option<u32>,
    column_comment: Option<String>,
    #[serde(default)]
    pub(crate) is_pk: bool,
    #[serde(default)]
    pub(crate) is_increment: bool,
    #[serde(default)]
    pub(crate) is_required: bool,
    #[serde(default)]
    pub(crate) sort: u16,
    default: Option<DefaultValueFunc>,
    update: Option<DefaultValueFunc>,
}

impl GenColumn {
    pub fn alias(&self) -> Alias {
        Alias::new(&self.column_name)
    }

    pub fn column_def(&self) -> sea_query::ColumnDef {
        let mut def = sea_query::ColumnDef::new(self.alias());
        if self.is_required {
            def.not_null();
        } else {
            def.null();
        }
        if self.is_increment {
            def.auto_increment();
        }
        match self.column_type {
            ColumnType::TinyInteger => def.tiny_integer(),
            ColumnType::TinyUnsigned => def.tiny_unsigned(),
            ColumnType::SmallInteger => def.small_integer(),
            ColumnType::SmallUnsigned => def.small_unsigned(),
            ColumnType::Integer => def.integer(),
            ColumnType::Unsigned => def.unsigned(),
            ColumnType::BigInteger => def.big_integer(),
            ColumnType::BigUnsigned => def.big_unsigned(),
            ColumnType::Boolean => def.boolean(),
            ColumnType::Text => def.text(),
            ColumnType::Json => def.json(),
            ColumnType::Float => def.float(),
            ColumnType::Double => def.double(),
            ColumnType::DateTime => def.date_time(),
            ColumnType::Timestamp => def.timestamp(),
            ColumnType::Time => def.time(),
            ColumnType::Date => def.date(),
            ColumnType::String => match self.column_type_len {
                Some(l) => def.string().string_len(l),
                None => def.string(),
            },
            ColumnType::Char => match self.column_type_len {
                Some(l) => def.char().char_len(l),
                None => def.char(),
            },
        };
        def
    }

    pub fn try_get_value_from(
        &self,
        query_result: &QueryResult,
        pre: &str,
    ) -> Result<JsonValue, sea_orm::DbErr> {
        self.column_type
            .map_query_value(query_result, pre, &self.column_name)
    }

    pub fn name(&self) -> &str {
        self.name.as_ref().unwrap_or(&self.column_name)
    }

    pub fn default_value_expr(&self) -> Option<SimpleExpr> {
        self.default.as_ref().map(Into::into)
    }

    pub fn get_value_expr_from(&self, v: &JsonValue) -> Option<SimpleExpr> {
        match self.column_type {
            ColumnType::Date
            | ColumnType::Time
            | ColumnType::DateTime
            | ColumnType::Text
            | ColumnType::String
            | ColumnType::Char => v.as_str().map(Into::into),
            ColumnType::Boolean => v.as_bool().map(Into::into),
            ColumnType::TinyInteger
            | ColumnType::SmallInteger
            | ColumnType::Integer
            | ColumnType::BigInteger => v.as_i64().map(Into::into),
            ColumnType::TinyUnsigned
            | ColumnType::SmallUnsigned
            | ColumnType::Unsigned
            | ColumnType::BigUnsigned => v.as_u64().map(Into::into),
            ColumnType::Float | ColumnType::Double => v.as_f64().map(Into::into),
            ColumnType::Timestamp => todo!(),
            ColumnType::Json => Some(v.to_string().into()),
        }
    }
}
